﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;
using System.Data.Common;
using System.Web;
using System.Web.Configuration;
namespace LG.DB
{
    public abstract class DBHelper
    {
        #region 字段和属性
        protected string _connectionString;
        protected Dictionary<string, object> _params;
        protected List<DbParameter> _outRetParams;
        
        private string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }
        public Dictionary<string, object> Params
        {
            get
            {
                if (_params == null)
                    _params = new Dictionary<string, object>();
                return _params;
            }
            set { _params = value; }
        }

        public List<DbParameter> OutRetParams
        {
            get
            {
                if (_outRetParams == null)
                    _outRetParams = new List<DbParameter>();
                return _outRetParams;
            }
        }
        #endregion

        public DBHelper(string content, ConnectionType type)
        {
            if (type == ConnectionType.ConnectionString)
            {
                _connectionString = content;
            }
            else if (type == ConnectionType.ConnectionName)
            {
                _connectionString = WebConfigurationManager.ConnectionStrings[content].ConnectionString;
            }
            else
            {
                throw new Exception("type参数ConnectionType枚举值");
            }
        }

        #region 子类要实现的抽象方法

        protected abstract void fillCmdParams_(DbCommand cmd);
        protected abstract DbCommand createCmd_(string cmdText, DbConnection conn);
        protected abstract DbCommand createCmd_();
        protected abstract DbDataAdapter createDa_(string cmdText, DbConnection conn);
        protected abstract DbConnection createConn_();
        public abstract DataTable GetPageData(string tblName, string strGetFields, string fldName, int pageSize, int pageIndex, bool isDesc, string strWhere, out int count);

        #endregion

        #region 私有方法

        private int do_(string text, CommandType type)
        {
            using (DbConnection conn = createConn_())
            {
                conn.Open();
                DbCommand cmd = createCmd_(text, conn);
                cmd.CommandType = type;
                fillCmdParams_(cmd);
                return cmd.ExecuteNonQuery();
            }
        }

        private DataSet doDateSet_(string text, CommandType type)
        {
            using (DbConnection conn = createConn_())
            {
                conn.Open();
                DbDataAdapter da = createDa_(text, conn);
                da.SelectCommand.CommandType = type;
                fillCmdParams_(da.SelectCommand);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
        }

        private object doScalar_(string text, CommandType type)
        {
            using (DbConnection conn = createConn_())
            {
                conn.Open();
                DbCommand cmd = createCmd_(text, conn);
                fillCmdParams_(cmd);
                cmd.CommandType = type;
                return cmd.ExecuteScalar();
            }
        }

        private DbDataReader doDataReader_(string text, CommandType type)
        {
            DbConnection conn = createConn_();
            try
            {
                conn.Open();
                DbCommand cmd = createCmd_(text, conn);
                cmd.CommandType = type;
                fillCmdParams_(cmd);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (DbException e)
            {
                conn.Close();
                throw e;
            }

        }

        #endregion

        #region 公共方法

        /// <summary>
        /// 执行一条sql语句，返回影响行数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public int Execute(string sql)
        {
            return do_(sql, CommandType.Text);
        }

        /// <summary>
        /// 执行一个存储过程，返回影响行数
        /// </summary>
        /// <param name="sp">存储过程</param>
        /// <returns></returns>
        public int Run(string sp)
        {
            return do_(sp, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 执行一条sql语句，返回DataRow
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataRow ExecDataRow(string sql)
        {
            DataTable dt = ExecDataTable(sql);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0];
            }
            return null;
        }

        /// <summary>
        /// 执行一个存储过程，返回DataRow
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataRow RunDataRow(string sql)
        {
            DataTable dt = RunDataTable(sql);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0];
            }
            return null;
        }

        /// <summary>
        /// 执行一条sql语句，返回DataTable
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DataTable ExecDataTable(string sql)
        {
            return ExecDataSet(sql).Tables[0];
        }

        /// <summary>
        /// 执行一个存储过程，返回DataTable
        /// </summary>
        /// <param name="sp">存储过程</param>
        /// <returns></returns>
        public DataTable RunDataTable(string sp)
        {
            return RunDataSet(sp).Tables[0];
        }

        /// <summary>
        /// 执行一条sql语句，返回DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet ExecDataSet(string sql)
        {
            return doDateSet_(sql, CommandType.Text);
        }

        /// <summary>
        /// 执行一个存储过程，返回DataSet
        /// </summary>
        /// <param name="sp">存储过程</param>
        /// <returns></returns>
        public DataSet RunDataSet(string sp)
        {
            return doDateSet_(sp, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 执行一条sql语句，返回首行首列
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public object ExecScalar(string sql)
        {
            return doScalar_(sql, CommandType.Text);
        }

        /// <summary>
        /// 执行一条sql语句，返回首行首列
        /// </summary>
        /// <typeparam name="T">类型参数</typeparam>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public T ExecScalar<T>(string sql)
        {
            return (T)ExecScalar(sql);
        }

        /// <summary>
        /// 执行一个存储过程，返回首行首列
        /// </summary>
        /// <param name="sp">存储过程</param>
        /// <returns></returns>
        public object RunScalar(string sp)
        {
            return doScalar_(sp, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 执行一个存储过程，返回首行首列
        /// </summary>
        /// <typeparam name="T">类型参数</typeparam>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public T RunScalar<T>(string sp)
        {
            return (T)RunScalar(sp);
        }

        /// <summary>
        /// 执行一条sql语句，返回DbDataReader
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DbDataReader ExecDataReader(string sql)
        {
            return doDataReader_(sql, CommandType.Text);
        }

        /// <summary>
        /// 执行一个存储过程，返回DbDataReader
        /// </summary>
        /// <param name="sp">存储过程</param>
        /// <returns></returns>
        public DbDataReader RunDataReader(string sp)
        {
            return doDataReader_(sp, CommandType.StoredProcedure);
        }
        
        /// <summary>
        /// 插入一条数据,参数写在Params里，名字与字段名一样
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public int Insert(string tableName)
        {
            StringBuilder sbp = new StringBuilder();
            StringBuilder sbn = new StringBuilder(")values(");
            sbp.AppendFormat("insert into [{0}] (", tableName);
            foreach (string name in Params.Keys)
            {
                sbp.AppendFormat("[{0}],", name);
                sbn.AppendFormat("@{0},", name);
            }
            sbp.Remove(sbp.Length - 1, 1);
            sbn.Remove(sbn.Length - 1, 1);
            string sqlAll = sbp.ToString() + sbn.ToString() + ")";
            return Execute(sqlAll);
        }

        /// <summary>
        /// 修改一条数据,参数写在Params里，名字与字段名一样
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="PKField">主键</param>
        /// <returns></returns>
        public int Update(string tableName, string PKField)
        {
            StringBuilder sbp = new StringBuilder();
            sbp.AppendFormat("update [{0}] set ", tableName);
            foreach (string name in _params.Keys)
            {
                if (name.ToUpper() != PKField.ToUpper())
                {
                    sbp.AppendFormat("[{0}]=@{0},", name);
                }
            }
            string strn = string.Format(" where [{0}]= @{0}", PKField);
            sbp.Remove(sbp.Length - 1, 1);
            string sqlAll = sbp.ToString() + strn;
            return Execute(sqlAll);
        }

        /// <summary>
        /// 修改一条数据,参数写在Params里，名字与字段名一样
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public int Update(string tableName)
        {
            return Update(tableName, "ID");
        }

        /// <summary>
        /// 执行一个事务
        /// </summary>
        /// <param name="transInfos">操作事务</param>
        /// <returns></returns>
        public bool ExecTrans(params TransInfo[] transInfos)
        {
            using (DbConnection conn = createConn_())
            {
                conn.Open();
                DbTransaction trans = conn.BeginTransaction();
                DbCommand cmd = createCmd_();
                cmd.Connection = conn;
                cmd.Transaction = trans;
                try
                {
                    foreach (TransInfo info in transInfos)
                    {
                        cmd.Parameters.Clear();
                        if (info.Params != null && info.Params.Count > 0)
                        {
                            foreach (DbParameter p in info.Params)
                            {
                                cmd.Parameters.Add(p);
                            }
                        }
                        cmd.CommandText = info.Text;
                        cmd.CommandType = info.Type;
                        info.AffectRows = cmd.ExecuteNonQuery();
                    }
                    trans.Commit();
                    return true;
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
            }
        }

        #endregion
    }

}
